package com.lovo.dao.impl;

import com.lovo.bean.CutPageBean;
import com.lovo.bean.DeptBean;
import com.lovo.bean.EmpBean;
import com.lovo.bean.SchoolBean;
import com.lovo.dao.BaseDao;
import com.lovo.dao.ISchoolDao;

import java.sql.SQLException;

public class SchoolDaoImpl extends BaseDao implements ISchoolDao {
    @Override
    public CutPageBean<SchoolBean> findByItem(int pageNo, String name, String status) {
        CutPageBean<SchoolBean> cutPageBean = new CutPageBean<>();
        String sql = "select * from school";
        String dynamicSql = " where status like '%"+status+"%'";
        if(name != null && name.length() > 0){
            dynamicSql += " and sc_name like '%"+name+"%'";
        }
        sql += dynamicSql + " limit "+(pageNo - 1) * CutPageBean.PAGESIZE+","+CutPageBean.PAGESIZE;
        try{
            setConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){

                cutPageBean.getList().add(fillSchool());
            }
            ps = con.prepareStatement("select count(*) cnt from school" +dynamicSql);
            rs = ps.executeQuery();
            rs.next();
            cutPageBean.setCount(rs.getInt("cnt"));

        }catch(Exception e){
            e.printStackTrace();
        }finally {
            closeConnection();
        }

        return cutPageBean;
    }

    @Override
    public void add(SchoolBean schoolBean) {
        try{
            this.setConnection();
            ps = con.prepareStatement("insert into school(sc_name,leader,addr,student_count,teacher_count,phone,info,city,status) values(?,?,?,?,?,?,?,?,?)");
            ps.setObject(1,schoolBean.getName());
            ps.setObject(2,schoolBean.getLeader());
            ps.setObject(3,schoolBean.getAddr());
            ps.setObject(4,schoolBean.getStudentCount());
            ps.setObject(5,schoolBean.getTeacherCount());
            ps.setObject(6,schoolBean.getPhone());
            ps.setObject(7,schoolBean.getInfo());
            ps.setObject(8,schoolBean.getCity());
            ps.setObject(9,schoolBean.getStatus());
            ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public void delete(int id) {
        try{
            this.setConnection();
            ps = con.prepareStatement("delete from school where sc_id=?");
            ps.setInt(1,id);
            ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public void update(SchoolBean schoolBean) {
        try {
            this.setConnection();
            ps = con.prepareStatement("update school set sc_name=?,leader=?,addr=?,student_count=?,teacher_count=?,phone=?,info=?,city=?,status=? where sc_id=?");
            ps.setObject(1, schoolBean.getName());
            ps.setObject(2, schoolBean.getLeader());
            ps.setObject(3, schoolBean.getAddr());
            ps.setObject(4, schoolBean.getStudentCount());
            ps.setObject(5, schoolBean.getTeacherCount());
            ps.setObject(6, schoolBean.getPhone());
            ps.setObject(7, schoolBean.getInfo());
            ps.setObject(8, schoolBean.getCity());
            ps.setObject(9, schoolBean.getStatus());
            ps.setObject(10, schoolBean.getId());
            ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public void updateStatus(int id, String status) {
        try {
            this.setConnection();
            ps = con.prepareStatement("update school set status=? where sc_id=?");
            ps.setObject(1, status);
            ps.setObject(2, id);
            ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }
    private SchoolBean fillSchool() throws SQLException {
        SchoolBean schoolBean = new SchoolBean();
        schoolBean.setId(rs.getInt("sc_id"));
        schoolBean.setName(rs.getString("sc_name"));
        schoolBean.setLeader(rs.getString("leader"));
        schoolBean.setAddr(rs.getString("addr"));
        schoolBean.setStudentCount(rs.getInt("student_count"));
        schoolBean.setTeacherCount(rs.getInt("teacher_count"));
        schoolBean.setPhone(rs.getString("phone"));
        schoolBean.setInfo(rs.getString("info"));
        schoolBean.setCity(rs.getString("city"));
        schoolBean.setStatus(rs.getString("status"));
        return schoolBean;
    }

    public static void main(String[] args) {
        SchoolDaoImpl schoolDao = new SchoolDaoImpl();
        System.out.println(schoolDao.findByItem(1,null,"接洽中"));

        SchoolBean schoolBean = new SchoolBean();
//        schoolBean.setId(5);
        schoolBean.setName("绵阳三中");
        schoolBean.setStatus("接洽中");
        schoolBean.setCity("绵阳");
        schoolDao.add(schoolBean);
//        schoolDao.delete(6);
//        schoolDao.update(schoolBean);
//        schoolDao.updateStatus(5,"审核未通过");
    }
}
